import pymysql
import requests
import json
import time

AllData = []

#处理数据
def extract_books(books_list, target_keys):
    return [{k: book[k] for k in target_keys if k in book} for book in books_list]

#爬取数据七猫小说数据
def qimao_():
    all_data = {}


    for i in range(1, 11):
        url = f"https://www.qimao.com/api/rank/book-list?is_girl=0&rank_type=1&date_type=1&date=202502&page={i}"
        cookies = {'19968543189': 'happyyzf@123'}
        headers = {
            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36 Edg/133.0.0.0'
        }
        rep = requests.get(url, headers=headers, cookies=cookies)
        rep.encoding = 'utf-8'
        all_data[i] = rep.text
        time.sleep(3)
        #data = json.loads(data)
        #all_data[i] = data['data']


    for i in range(1,len(all_data)):
        data = all_data[i]
        data = json.loads(data)
        data = data["data"]
        # 提取并输出结果
        target_keys = ['book_id', 'title', 'author', 'category1_name', 'category2_name', 'words_num', 'number']
        result = extract_books(data['table_data'], target_keys)

        for item in result:
            AllData.append(item)


    print(len(AllData))
    return AllData


#插入数据库
def insert_():
    conn = pymysql.connect(host="localhost", user="root", password="happyyzf@123", db="python_qimiao", port=3306)
    print(conn)
    cur = conn.cursor()
    try:

        data = qimao_()
        sql = "insert into bd (book_id,title,author,category1_name,category2_name,words_num,number) values (%s,%s,%s,%s,%s,%s,%s) "

        for item in data:
            cur.execute(sql,(item['book_id'], item['title'], item['author'], item['category1_name'], item['category2_name'], item['words_num'], item['number']))

        # 提交
        conn.commit()
    except Exception as  e:
        conn.rollback()  # 回滚事务
        print(f"插入失败：{e}")
    finally:
        cur.close()
        conn.close()







if __name__ == "__main__":
    insert_()






